package cn.tiger.config;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;

/**
 * 数据库迁移组件
 * 
 * 在应用启动时自动执行数据库迁移脚本，确保数据库结构与实体类保持同步
 * 仅在开发环境中使用，生产环境应使用专业的数据库迁移工具
 * 
 * @author AI Backend Team
 * @version 1.0
 * @since 2025-09-26
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class DatabaseMigration implements ApplicationRunner {
    
    private final JdbcTemplate jdbcTemplate;
    
    @Override
    public void run(ApplicationArguments args) throws Exception {
        log.info("开始执行数据库迁移...");
        
        try {
            // 检查user表是否存在membership_type字段
            if (!checkColumnExists("user", "membership_type")) {
                log.info("执行会员功能数据库迁移...");
                executeMigrationScript();
                log.info("数据库迁移完成！");
            } else {
                log.info("数据库已经是最新版本，跳过迁移。");
            }
            
        } catch (Exception e) {
            log.error("数据库迁移失败", e);
            // 不阻止应用启动，允许手动修复
        }
    }
    
    /**
     * 检查表中是否存在指定字段
     */
    private boolean checkColumnExists(String tableName, String columnName) {
        try {
            String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS " +
                        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName, columnName);
            return count != null && count > 0;
        } catch (Exception e) {
            log.warn("检查字段{}是否存在时出错: {}", columnName, e.getMessage());
            return false;
        }
    }
    
    /**
     * 执行迁移脚本
     */
    private void executeMigrationScript() {
        try {
            // 手动执行迁移操作，而不是读取文件
            executeMigrationSteps();
            
        } catch (Exception e) {
            log.error("执行迁移脚本时出错", e);
            throw new RuntimeException("数据库迁移失败", e);
        }
    }
    
    /**
     * 执行具体的迁移步骤
     */
    private void executeMigrationSteps() {
        try {
            // 1. 添加membership_type字段
            if (!checkColumnExists("user", "membership_type")) {
                String sql1 = "ALTER TABLE `user` ADD COLUMN `membership_type` VARCHAR(20) DEFAULT 'FREE' COMMENT '会员类型：FREE-免费用户，VIP-会员用户' AFTER `status`";
                jdbcTemplate.execute(sql1);
                log.info("添加membership_type字段成功");
            }
            
            // 2. 添加membership_expire_time字段
            if (!checkColumnExists("user", "membership_expire_time")) {
                String sql2 = "ALTER TABLE `user` ADD COLUMN `membership_expire_time` DATETIME COMMENT '会员到期时间' AFTER `membership_type`";
                jdbcTemplate.execute(sql2);
                log.info("添加membership_expire_time字段成功");
            }
            
            // 3. 添加索引
            if (!checkIndexExists("user", "idx_membership")) {
                String sql3 = "ALTER TABLE `user` ADD INDEX `idx_membership` (`membership_type`, `membership_expire_time`)";
                jdbcTemplate.execute(sql3);
                log.info("添加会员索引成功");
            }
            
            // 4. 创建membership表
            if (!checkTableExists("membership")) {
                String sql4 = "CREATE TABLE `membership` (" +
                    "`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '会员记录ID'," +
                    "`user_id` BIGINT NOT NULL COMMENT '用户ID'," +
                    "`membership_type` VARCHAR(20) NOT NULL DEFAULT 'FREE' COMMENT '会员类型：FREE-免费用户，VIP-会员用户'," +
                    "`start_time` DATETIME COMMENT '会员开始时间'," +
                    "`expire_time` DATETIME COMMENT '会员到期时间'," +
                    "`amount` DECIMAL(10,2) COMMENT '充值金额'," +
                    "`charge_time` DATETIME COMMENT '充值时间'," +
                    "`status` TINYINT DEFAULT 1 COMMENT '状态：1-有效，0-失效'," +
                    "`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'," +
                    "`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
                    "PRIMARY KEY (`id`)," +
                    "KEY `idx_user_id` (`user_id`)," +
                    "KEY `idx_user_type` (`user_id`, `membership_type`)," +
                    "KEY `idx_expire_time` (`expire_time`)," +
                    "KEY `idx_status` (`status`)," +
                    "CONSTRAINT `fk_membership_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员表'";
                jdbcTemplate.execute(sql4);
                log.info("创建membership表成功");
            }
            
            // 5. 更新现有用户的默认会员类型
            String sql5 = "UPDATE `user` SET `membership_type` = 'FREE' WHERE `membership_type` IS NULL OR `membership_type` = ''";
            int updatedRows = jdbcTemplate.update(sql5);
            log.info("更新{}个用户的默认会员类型", updatedRows);
            
        } catch (Exception e) {
            log.error("执行迁移步骤失败", e);
            throw e;
        }
    }
    
    /**
     * 检查表是否存在
     */
    private boolean checkTableExists(String tableName) {
        try {
            String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES " +
                        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName);
            return count != null && count > 0;
        } catch (Exception e) {
            log.warn("检查表{}是否存在时出错: {}", tableName, e.getMessage());
            return false;
        }
    }
    
    /**
     * 检查索引是否存在
     */
    private boolean checkIndexExists(String tableName, String indexName) {
        try {
            String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS " +
                        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName, indexName);
            return count != null && count > 0;
        } catch (Exception e) {
            log.warn("检查索引{}是否存在时出错: {}", indexName, e.getMessage());
            return false;
        }
    }
}